package org.chipu.jnids.controller;

import java.io.File;
import java.net.URISyntaxException;
import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.chipu.jnids.alert.SnortEvent;
import org.chipu.jnids.beans.Flow;
import org.chipu.jnids.beans.Profile;
import org.chipu.shared.db.DatabaseManager;
import org.chipu.shared.db.STExecuter;

public class DBLayer {
	private final DatabaseManager dbManager;
	private final Logger logger;
	public static final String initialDataTable = "sancp_lines";
	private static final String startTimeColumn = "start_time_gmt";
	private static final String profilesTable = "profiles";
	private static final String currentFlowsTable = "current_flows";
	private static final String alertsTable = "alerts";

	public DBLayer(DatabaseManager dbManager, Logger logger) throws URISyntaxException {
		this.dbManager = dbManager;
		this.logger = logger == null? Logger.getLogger(getClass().getSimpleName()): logger;
		dbManager.executeBatchCommand("CREATE DATABASE IF NOT EXISTS jnids", false);
		createTables();
	}

	private void createTables() throws URISyntaxException {
		File sql = new File(getClass().getResource("/org/chipu/jnids/controller/tables.sql").toURI());
		dbManager.executeBatchFile(sql);
	}

	// ----- Initial data filling

	public void recreateSancpTable(String[] pattern, Map<String, Class<?>> types) throws Exception {
		dbManager.execute("DROP TABLE IF EXISTS " + initialDataTable);
		StringBuilder s = new StringBuilder();
		s.append("CREATE TABLE ").append(initialDataTable).append('(');
		for(String field : pattern)
			s.append(field).append(' ').append(DatabaseManager.getDBType(types.get(field))).append(',');
		s.replace(s.length() - 1, s.length(), "");
		s.append(')');
		dbManager.execute(s.toString());
	}

	public void addSancpData(String sql) throws Exception {
		dbManager.execute(sql);
	}

	/** Creates the index to increase the performance when creating the profile */
	public void createSancpTimeIndex() throws Exception {
		dbManager.execute("CREATE INDEX time_index ON " + initialDataTable + "(" + startTimeColumn + ")");
	}

	/** @return The time in seconds */
	public int getTimeOfFirstSancpFlow() throws Exception {
		return dbManager.getIntDataFromDB("SELECT min(UNIX_TIMESTAMP(" + startTimeColumn + ")) FROM "
			+ initialDataTable);
	}

	/** @return The time in seconds */
	public int getTimeOfLastSancpFlow() throws Exception {
		return dbManager.getLongDataFromDB(
			"SELECT max(UNIX_TIMESTAMP(" + startTimeColumn + ")+duration) FROM " + initialDataTable)
			.intValue();
	}

	public List<Flow> getFlowsAt(int timeInSeconds) {
		final String sql = "SELECT * FROM " + initialDataTable + " WHERE UNIX_TIMESTAMP(" + startTimeColumn
			+ ")<" + timeInSeconds + " AND (SELECT " + "(SELECT UNIX_TIMESTAMP(" + startTimeColumn + "))"
			+ "+duration)>" + timeInSeconds + " ORDER BY " + startTimeColumn;
		try {
			return dbManager.getDataFromDB(new STExecuter<List<Flow>>() {
				@Override
				public List<Flow> execute(Statement st) throws SQLException {
					ArrayList<Flow> list = new ArrayList<Flow>();
					ResultSet rs = st.executeQuery(sql);
					while(rs.next())
						list.add(new Flow(rs.getInt("ip_proto"), (int) (rs.getDate("start_time_gmt")
							.getTime() / 1000), rs.getInt("duration"), rs.getString("src_ip_dotted"), rs
							.getString("dst_ip_dotted"), rs.getInt("src_port"), rs.getInt("dst_port")));
					return list;
				}
			});
		} catch(Exception ex) {
			logger.log(Level.SEVERE, "Query: " + sql, ex);
			return Collections.emptyList();
		}
	}

	// ----- Profile

	public void cleanProfile() {
		logger.info("Cleaning previous profile");
		dbManager.dropTable(initialDataTable);
		try {
			createTables();
		} catch(URISyntaxException ex) {
			ex.printStackTrace();
		}
	}

	public boolean isThereAProfileStored() {
		// IMPROVE check that the data in the table is complete
		return dbManager.existsTable(initialDataTable) && !dbManager.isTableEmpty(initialDataTable);
	}

	public void addProfile(Profile profile) throws Exception {
		dbManager.execute("INSERT INTO " + profilesTable + " (name,blacklist,value) VALUES ('"
			+ profile.getName() + "'," + (profile.isValidProfile()? 1: 0) + ",'" + profile.encode() + "')");
	}

	public Profile getProfile(final String name) throws Exception {
		return dbManager.getDataFromDB(new STExecuter<Profile>() {
			@Override
			public Profile execute(Statement st) throws Exception {
				ResultSet rs = st.executeQuery("SELECT value FROM " + profilesTable + " WHERE name='" + name
					+ "'");
				if (rs.next())
					return Profile.decode(rs.getString("value"));
				return null;
			}
		});
	}

	// ----- Alerts

	public void addDetectedAlert(SnortEvent event) {
	// TODO addDetectedAlert to DB
	}

	public void markAlertAsSafe(SnortEvent event) {
	// TODO markAlertAsSafe in DB
	}

	public Profile createNewProfileFromDetectedAlerts() {
		// TODOLATER create profile from alerts
		// mark used alerts to not use them any more to build a new profile
		return null;
	}
}